/*
 * @(#)GSPayReceiveAbleDAO.java
 * 
 * Project: BUS
 * 
 * Modify Information:
 * =============================================================================
 * Author          Date         Description
 *  ------------ ---------- ------------------------------------- 
 *  simonyi peng  2006-4-10    创建
 *  Jenny   Li    2006-5-16    添加方法insertPayReceiveInfo,queryPayRecvInfo,deletePayRecvInfo
 *                              queryUnCompletedPayRecvInfo,queryUnCompletedPayRecvInfoCount,
 *                              getUnCompletedPayRecvOrgSql,getUnCompletedPayRecvProvSql,
 *                              updatePayRecvAbleInfo
 *  Jenny   Li    2006-6-9     添加方法queryCurMonthPayRecvInfo                         
 * Copyright Notice:
 * =============================================================================
 * Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room,
 * Science & Technology Building, No. 9 South Street, Zhong Guan Cun, Haidian
 * District, Beijing ,100081, China All rights reserved.
 * 
 * This software is the confidential and proprietary information of Beijing
 * HiTRUST Technology Co., Ltd. ("Confidential Information"). You shall not
 * disclose such Confidential Information and shall use it only in accordance
 * with the terms of the license agreement you entered into with HiTRUST.
 * 
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.dao;

import java.sql.Connection;
import java.util.ArrayList;

import com.hitrust.gs.bean.GSPayReceiveAbleBean;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSBalPayRecvManageQryResultVO;
import com.hitrust.gs.vo.GSBalPayRecvManageVO;
import com.hitrust.gs.vo.GSBalanceManageQueryVO;
import com.hitrust.gs.vo.GSPayReceiveListVO;
//import com.hitrust.gs.vo.GSTempOrderVO;

public class GSPayReceiveAbleDAO  extends GSBaseDAO {


    

    /**
     * 查询订单详细信息，FOR订单录入的结果页面显示
     * @param aOrderId
     * @return
     * @throws GSException
     */
    public ArrayList queryPayReceiveAble(String tSql) throws GSException {
        ArrayList tList = new ArrayList();
        
        try{
            tList = executeQuery(new GSPayReceiveListVO(),tSql.toString(),null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
        
    }
    
    /**
     * 根据复合id查询用户指定的应收应付记录
     * @param conn
     * @param ids
     * @return
     * @throws GSException
     */
    public ArrayList queryPayReceiveAble(Connection conn, String[] ids) throws GSException {
        ArrayList tList = new ArrayList();
        String tSql = "select * from gs_pay_receive_able where GS_RECEIVEABLE_ID=?";
        try{
            for(int i=0;i<ids.length;i++){
                Object[] objs = new Object[]{ids[i]};
                ArrayList ary = executeQuery(conn,new GSPayReceiveAbleBean(),tSql,objs);
                tList.add(ary.get(0));
            }
            
        }catch(Exception e){
            throw new GSException("","");
        }
        return tList;
    }
    /**
     * 新增应收应付信息
     * @param aBean         新增数据
     * @param aConn         DB连接
     * @return
     * @throws GSException
     */
	public boolean insertPayReceiveInfo(GSPayReceiveAbleBean aBean,Connection aConn) throws GSException{
		boolean tFlag = false;
		StringBuffer tSql = new StringBuffer("insert into GS_PAY_RECEIVE_ABLE(GS_WORKCORP_ID,GS_WORKCORP_CATEGORY,");
			tSql.append("GS_BALANCE_MONTH,GS_NOTES,GS_WORKCORP_LEVEL,GS_WORKCORP_ID_GROUP,GS_FEE_TYPE_ID,");
			tSql.append("GS_PAY_DATE,GS_PAY_TYPE,GS_BALANCE_NEED,GS_BALANCE_DONE,GS_BALANCE_UNDONE,GS_OFFSET_DATE,");
			tSql.append("GS_PAY_STATUS,GS_PRINCIPAL,GS_OPERATOR_ID,GS_OPERATE_DATE,GS_RECEIVEABLE_ID)values(?,?,?,?,?,?,?,?,?,?,?,?");
			tSql.append(",?,?,?,?,?,?)");
		Object[] tObj = new Object[18];
		tObj[0] = aBean.getGs_workcorp_id();
		tObj[1] = aBean.getGs_workcorp_category();
		tObj[2] = aBean.getGs_balance_month();
		tObj[3] = aBean.getGs_notes();
		tObj[4] = aBean.getGs_workcorp_level();
		tObj[5] = aBean.getGs_workcorp_id_group();
		tObj[6] = aBean.getGs_fee_type_id();
		tObj[7] = aBean.getGs_pay_date();
		tObj[8] = aBean.getGs_pay_type();
		tObj[9] = String.valueOf(aBean.getGs_balance_need());
		tObj[10] = String.valueOf(aBean.getGs_balance_done());
		tObj[11] = String.valueOf(aBean.getGs_balance_undone());
		tObj[12] = aBean.getGs_offset_date();
		tObj[13] = aBean.getGs_pay_status();
		tObj[14] = aBean.getGs_principal();
		tObj[15] = aBean.getGs_operator_id();
		tObj[16] = aBean.getGs_operator_date();
		tObj[17] = aBean.getGs_receiveable_id();
		try{
			tFlag = this.executeUpdate(aConn,tSql.toString(),tObj);
		}catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19995,GSException.E19995_MSG);  
		}
		return tFlag;
	}
    /**
     * 查询应收应付信息
     * @param aConn             DB连接
     * @param aBalYM            结算月份
     * @param aDeptId           单位编号
     * @return
     * @throws GSException
     */
	public ArrayList queryPayRecvInfo(Connection aConn,String aBalYM,String aDeptId) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = "select * from gs_pay_receive_able where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?";
        Object[] tObj = new Object[2];
        tObj[0] = aDeptId;
        tObj[1] = aBalYM;
        try{
            tList = executeQuery(aConn,new GSPayReceiveAbleBean(),tSql,tObj);
        }catch(Exception e){
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 删除应收应付信息
     * @param aConn             DB连接
     * @param aPayRecAbleBean   删除条件
     * @return
     * @throws GSException
     */
	public boolean deletePayRecvInfo(Connection aConn,GSPayReceiveAbleBean aPayRecAbleBean) throws GSException{
	    boolean tFlag = false;
        String tSql = "delete from gs_pay_receive_able where GS_WORKCORP_ID=? and GS_BALANCE_MONTH=?";
        Object[] tObj = new Object[2];
        tObj[0] = aPayRecAbleBean.getGs_workcorp_id();
        tObj[1] = aPayRecAbleBean.getGs_balance_month();
        try{
            tFlag = executeUpdate(aConn,tSql,tObj);
        }catch(Exception e){
            throw new GSException(GSException.E19995,GSException.E19995_MSG);
        }
        return tFlag;
    }
    /**
     * 查询未完成或部分完成的应收应付信
     * @param aVO               查询条件
     * @param pageNo            页号
     * @param pageSize          页笔数
     * @param aOrg              订货单位
     * @return
     * @throws GSException
     */
    public ArrayList queryUnCompletedPayRecvInfo(GSBalanceManageQueryVO aVO,int pageNo,int pageSize,ArrayList aOrg) throws GSException{
        ArrayList tList = new ArrayList();
        StringBuffer tSql = new StringBuffer();
        if(aVO.deptCategory.equals("0")){
            tSql.append(getUnCompletedPayRecvOrgSql(aVO,aOrg));
            tSql.append(" union ");
            tSql.append(getUnCompletedPayRecvProvSql(aVO));
        }else if(aVO.deptCategory.equals(GSBaseConstants.BALANCE_ORDERDEPT_TYPE)){
            tSql.append(getUnCompletedPayRecvOrgSql(aVO,aOrg));
        }else if(aVO.deptCategory.equals(GSBaseConstants.BALANCE_PROVIDER_TYPE)){
            tSql.append(getUnCompletedPayRecvProvSql(aVO));
        }
        GSLogWriter.info(tSql.toString());
        try{
            tList = this.executeQuery(new GSBalPayRecvManageQryResultVO(),tSql.toString(),null,pageNo,pageSize);
        }catch(Exception e){
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 查询未完成或部分完成的应收应付信息笔数
     * @param aVO           查询条件
     * @param aOrg          订货单位
     * @return
     * @throws GSException
     */
    public int queryUnCompletedPayRecvInfoCount(GSBalanceManageQueryVO aVO,ArrayList aOrg) throws GSException{
        int tSize = 0;
        StringBuffer tSql = new StringBuffer("select count(*) from(");
        if(aVO.deptCategory.equals("0")){
            tSql.append(getUnCompletedPayRecvOrgSql(aVO,aOrg));
            tSql.append(" union ");
            tSql.append(getUnCompletedPayRecvProvSql(aVO));
        }else if(aVO.deptCategory.equals(GSBaseConstants.BALANCE_ORDERDEPT_TYPE)){
            tSql.append(getUnCompletedPayRecvOrgSql(aVO,aOrg));
        }else if(aVO.deptCategory.equals(GSBaseConstants.BALANCE_PROVIDER_TYPE)){
            tSql.append(getUnCompletedPayRecvProvSql(aVO));
        }
        tSql.append(")");
        try{
            tSize = this.queryRowNum(tSql.toString(),null);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tSize;
    }
    /**
     * 查询未完成或部分完成与订货单位相关的应收应付信息的SQL
     * @param aVO           查询条件
     * @param aOrg          订货单位
     * @return
     * @throws GSException
     */
    private String getUnCompletedPayRecvOrgSql(GSBalanceManageQueryVO aVO,ArrayList aOrg) throws GSException{
        StringBuffer tSql = new StringBuffer("select B.GS_ORG_NAME as P1,A.GS_WORKCORP_CATEGORY as P2,");
        tSql.append(" A.GS_FEE_TYPE_ID as P3,A.GS_PAY_TYPE as P4,A.GS_BALANCE_NEED as P5,A.GS_BALANCE_DONE as P6,");
        tSql.append(" A.GS_BALANCE_UNDONE as P7,A.GS_PAY_DATE as P8,A.GS_PRINCIPAL as P9,A.GS_PAY_STATUS as P10,");
        tSql.append(" A.GS_BALANCE_MONTH as P11,A.GS_WORKCORP_ID as P12,A.GS_RECEIVEABLE_ID as P13");
        tSql.append(" from GS_PAY_RECEIVE_ABLE A,GS_ORGANIZATION B where ");
        tSql.append(" A.GS_WORKCORP_ID=B.GS_ORG_ID");
        if(!aVO.orderDeptId.equals("0000")){
            tSql.append(" and A.GS_WORKCORP_ID='" + aVO.orderDeptId + "'");
        }else{
            if(aOrg.size() > 0){
                tSql.append(" and A.GS_WORKCORP_ID in(");
                for(int i=0; i<aOrg.size();i++){
                    String tStr[] = (String[])aOrg.get(i);
                        if( i == aOrg.size()-1){
                            tSql.append("'"+tStr[0]+"')");
                        }else{
                            tSql.append("'"+tStr[0]+"',");
                        }
                }
            }
        }
        if(!aVO.payType.equals("0")){
            tSql.append(" and A.GS_PAY_TYPE='" + aVO.payType + "'");
        }else{
            tSql.append(" and (A.GS_PAY_TYPE='2' or A.GS_PAY_TYPE='1')");
        }
        if(!aVO.status .equals("00")){
            tSql.append(" and A.GS_PAY_STATUS = '" + aVO.status + "'");
        }else{
            tSql.append(" and (A.GS_PAY_STATUS='1' or A.GS_PAY_STATUS = '2')");
        }
        if(aVO.payRecvEndDate != null){
            tSql.append(" and to_char(to_date(A.GS_PAY_DATE,'YYYY-MM-DD'),'YYYYMMDD') <='" + aVO.payRecvEndDate + "'");
        }
        if(aVO.payRecvStrDate != null){
            tSql.append(" and to_char(to_date(A.GS_PAY_DATE,'YYYY-MM-DD'),'YYYYMMDD') >='" + aVO.payRecvStrDate + "'");
        }
        
        return tSql.toString();
    }
    /**
     * 查询未完成或部分完成与供应商相关的应收应付信息的SQL
     * @param aVO           查询条件
     * @return
     * @throws GSException
     */
    private String getUnCompletedPayRecvProvSql(GSBalanceManageQueryVO aVO) throws GSException{
        StringBuffer tSql = new StringBuffer("select B.GS_PROVIDER_SHORTNAME as P1,A.GS_WORKCORP_CATEGORY as P2,");
        tSql.append(" A.GS_FEE_TYPE_ID as P3,A.GS_PAY_TYPE as P4,A.GS_BALANCE_NEED as P5,A.GS_BALANCE_DONE as P6,");
        tSql.append(" A.GS_BALANCE_UNDONE as P7,A.GS_PAY_DATE as P8,A.GS_PRINCIPAL as P9,A.GS_PAY_STATUS as P10,");
        tSql.append(" A.GS_BALANCE_MONTH as P11,A.GS_WORKCORP_ID as P12,A.GS_RECEIVEABLE_ID as P13");
        tSql.append(" from GS_PAY_RECEIVE_ABLE A,GS_PROVIDER B where ");
        tSql.append(" A.GS_WORKCORP_ID=B.GS_PROVIDER_ID");
        if(aVO.provider != null && !aVO.provider.trim().equals("")){
            tSql.append(" and B.GS_PROVIDER_SHORTNAME ='" + aVO.provider + "'");
        }
        
        if(!aVO.payType.equals("0")){
            tSql.append(" and A.GS_PAY_TYPE='" + aVO.payType + "'");
        }else{
            tSql.append(" and (A.GS_PAY_TYPE='2' or A.GS_PAY_TYPE='1')");
        }
        if(!aVO.status.equals("00")){
            tSql.append(" and A.GS_PAY_STATUS = '" + aVO.status + "'");
        }else{
            tSql.append(" and (A.GS_PAY_STATUS='1' or A.GS_PAY_STATUS = '2')");
        }
        if(aVO.payRecvEndDate != null){
            tSql.append(" and to_char(to_date(A.GS_PAY_DATE,'YYYY-MM-DD'),'YYYYMMDD') <='" + aVO.payRecvEndDate + "'");
        }
        if(aVO.payRecvStrDate != null){
            tSql.append(" and to_char(to_date(A.GS_PAY_DATE,'YYYY-MM-DD'),'YYYYMMDD') >='" + aVO.payRecvStrDate + "'");
        }
        
        return tSql.toString();
    }
    /**
     * 修改应收应付信息
     * @param aVO       待修改数据
     * @param aOperId   操作员编码
     * @return
     * @throws GSException
     */
    public boolean updatePayRecvAbleInfo(GSBalPayRecvManageVO aVO,String aOperId) throws GSException{
        boolean tFlag = false;
        StringBuffer tSql = new StringBuffer("update GS_PAY_RECEIVE_ABLE A set A.GS_PAY_DATE=?,A.GS_PRINCIPAL=?,");
        tSql.append(" A.GS_OPERATOR_ID=? ,A.GS_OPERATE_DATE=?");
//        tSql.append(" where A.GS_WORKCORP_ID=? and A.GS_BALANCE_MONTH=? and A.GS_FEE_TYPE_ID=?");
        tSql.append(" where A.GS_RECEIVEABLE_ID=?");        //修改主键
        Object[] tObj = new Object[5];
        tObj[0] = aVO.payDate;
        tObj[1] = aVO.principal;
        tObj[2] = aOperId;
        tObj[3] = DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss");
//        tObj[4] = aVO.deptId;
//        tObj[5] = aVO.balanceYM;
//        tObj[6] = aVO.feeTypeNo;
        tObj[4] = aVO.receiveableId;
        try{
            tFlag = this.executeUpdate(tSql.toString(),tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19995,GSException.E19995_MSG);
        }
        return tFlag;
    }
    /**
     * 查询指定月份的应收应付信息
     * @param aMonth            月份
     * @param aPayType          支付类别
     * @return
     * @throws GSException
     */
    public ArrayList queryCurMonthPayRecvInfo(String aBegin,String aEnd,String aPayType) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = "select * from GS_PAY_RECEIVE_ABLE where GS_PAY_DATE>=? and GS_PAY_DATE <=? and GS_PAY_TYPE=? and GS_PAY_STATUS in (2,1)";
        Object[] tObj = {aBegin,aEnd,aPayType};
        try{
            tList = executeQuery(new GSPayReceiveAbleBean(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
}
